﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL;
using ECS.DAL.Entity;
namespace ECS.DAL.Component
{
    public class ZoneComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT [zone_id] ,[wh_master_id] ,[zone], [description], [zone_type], [is_active] ," +
                                        "[create_date], [create_by] " +
                                        " FROM [dbo].[t_eics_zone] ";

        private const string SQL_PROC_QUERY = "";

        private const string SQL_INSERT = "";

        private const string SQL_PROC_INSERT = "usp_InsertZoneRecord "+
            "@in_vchApplicationID =[@AppId], @in_vchWhMasterID=[@WhId], @in_vchZone=[@ZoneName], @in_vchDescription=[@Description], @in_vchZoneType=[@ZoneType], " +
            "@in_vchIsActive=[@IsActive], @in_vchDevice=[@Device], @in_vchUserID=[@UserID] ";
        
        private const string SQL_UPDATE = "";

        private const string SQL_PROC_UPDATE = "usp_UpdateZoneRecord " +
            "@in_vchApplicationID =[@AppId], @in_vchZoneID=[@ZoneId], @in_vchWhMasterID=[@WhId], @in_vchZone=[@ZoneName], @in_vchDescription=[@Description], @in_vchZoneType=[@ZoneType], " +
            "@in_vchIsActive=[@IsActive], @in_vchDevice=[@Device], @in_vchUserID=[@UserID] ";

        private const string SQL_DELETE = "";

        private const string SQL_PROC_DELETE = "usp_DeleteZoneRecord @in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], " +
                                    " @in_vchUserID = [@UserId] ,@in_vchZoneID=[@ZoneId], @in_vchIsActive = [@IsActive]";

        private const string SQL_GET_WH_MASTER_ID = "SELECT wh_master_id FROM t_eics_wh";

        private const string dataTableName = "Zone";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public ZoneComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        private void ClearErrorMessage()
        {
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
        }
        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            Zone param = null;
            param = parameter == null ? null : (Zone)parameter;
            using (Entities entities = new Entities())
            {
                entities.Open();
                sqlString.Append(SQL_QUERY);
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Zone data = new Zone();
                    data.ZoneId = base.GetString(0, reader);
                    data.WhMasterId = base.GetString(1, reader);
                    data.ZoneName = base.GetString(2, reader);
                    data.Description = base.GetString(3, reader);
                    data.ZoneType = base.GetString(4, reader);
                    data.IsActive = base.GetString(5, reader);
                    data.CreateDate= (DateTime)base.GetDate(6, reader);
                    data.CreateBy = base.GetString(7, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }


        public override string GetInsertSQL(ObjectContext param)
        {
            /* " (@setup_id ,@station_id ,@type ,@setting_name ,@setting_value_string " +
          " ,@setting_value_number ,@setting_value_datetime ,@create_date,@create_by) ";*/
            Zone parameter = (Zone)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@WhId]", BaseSQLModel.SqlWithQoute(parameter.WhMasterId));
            sqlString.Replace("[@ZoneName]", BaseSQLModel.SqlWithQoute(parameter.ZoneName));
            sqlString.Replace("[@Description]", BaseSQLModel.SqlWithQoute(parameter.Description));
            sqlString.Replace("[@ZoneType]", BaseSQLModel.SqlWithQoute(parameter.ZoneType));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserID]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            return sqlString.ToString();


            
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            Zone parameter = (Zone)param;
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append(SQL_PROC_UPDATE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@ZoneId]", BaseSQLModel.SqlWithQoute(parameter.ZoneId));
            sqlString.Replace("[@WhId]", BaseSQLModel.SqlWithQoute(parameter.WhMasterId));
            sqlString.Replace("[@ZoneName]", BaseSQLModel.SqlWithQoute(parameter.ZoneName));
            sqlString.Replace("[@Description]", BaseSQLModel.SqlWithQoute(parameter.Description));
            sqlString.Replace("[@ZoneType]", BaseSQLModel.SqlWithQoute(parameter.ZoneType));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserID]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            return sqlString.ToString();
            /*
             "@in_vchApplicationID =[@AppId], @in_vchZoneID=[@ZoneId], @in_vchWhMasterID=[@WhId], @in_vchZone=[@ZoneName], @in_vchDescription=[@Description], @in_vchZoneType=[@ZoneType], " +
            "@in_vchIsActive=[@IsActive], @in_vchDevice=[@Device], @in_vchUserID=[@UserID] ";
             */
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            Zone parameter = (Zone)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_DELETE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@ZoneId]", BaseSQLModel.SqlWithQoute(parameter.ZoneId));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
          
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("Zone insert unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            DataSet ds = new DataSet();
            ClearErrorMessage();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            DataSet ds = new DataSet();
            ClearErrorMessage();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string whMasterId = string.Empty;
            Zone parameter = null;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            ClearErrorMessage();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(SQL_GET_WH_MASTER_ID);
                    DbDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        whMasterId = GetString(0, reader);
                    }
                    reader.Close();
                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (Zone)item;
                        
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                //master id
                                parameter.WhMasterId = whMasterId;

                                sqlCmd = GetInsertSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", BaseSQLModel.SqlWithQoute(parameter.AppId)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchWhMasterID", BaseSQLModel.SqlWithQoute(parameter.WhMasterId)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchZone", BaseSQLModel.SqlWithQoute(parameter.ZoneName)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDescription", BaseSQLModel.SqlWithQoute(parameter.Description)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchZoneType", BaseSQLModel.SqlWithQoute(parameter.ZoneType)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchIsActive", BaseSQLModel.SqlWithQoute(parameter.IsActive)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", BaseSQLModel.SqlWithQoute(parameter.Device)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", BaseSQLModel.SqlWithQoute(parameter.UserId)));
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.Text;
                                //cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", BaseSQLModel.SqlWithQoute(parameter.AppId)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchZoneID", BaseSQLModel.SqlWithQoute(parameter.ZoneId)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchWhMasterID", BaseSQLModel.SqlWithQoute(parameter.WhMasterId)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchZone", BaseSQLModel.SqlWithQoute(parameter.ZoneName)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDescription", BaseSQLModel.SqlWithQoute(parameter.Description)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchZoneType", BaseSQLModel.SqlWithQoute(parameter.ZoneType)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchIsActive", BaseSQLModel.SqlWithQoute(parameter.IsActive)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", BaseSQLModel.SqlWithQoute(parameter.Device)));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", BaseSQLModel.SqlWithQoute(parameter.UserId)));
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }
                    foreach (ObjectContext item in items)
                    {
                        item.NotChangeObject();
                    }
                }
                transaction.Complete();
                items.RemoveAll(o => o.ObjectStatus == OBJECT_STATE.Delete);
                foreach (ObjectContext item in items)
                { 
                    item.NotChangeObject();
                }
            }
        }
        #endregion
    }
}
